﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;
using Sinacor.Infra.Service.Common;

namespace Sinacor.Servico.Bovespa.Corretagem.ResourceAccess
{
    /// <summary>
    /// Taxa
    /// </summary>
    public class Taxa : BaseResourceAccess
    {
        #region Construtor
        /// <summary>
        /// Taxa
        /// </summary>
        public Taxa()
            : base(Sinacor.Infra.Service.Security.Authorization.AuthorizationContext.Current.CompanyId)
        {
        }

        #endregion

        #region [PUB] AlterarTaxa
        public void AlterarTaxa(BusinessEntity.Taxa request)
        {
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                #region SQL
                StringBuilder sql = new StringBuilder();

                sql.Append(@" UPDATE TORTXEMU
                                        SET NR_EMUBOV = :PercEmoluentoBovespa / 100, 
                                            NR_EMUCBL = :PercEmoluentoCBLC / 100,
                                            NR_REGBOV = :PercRegistroBovespa / 100,
                                            NR_REGCBL = :PercRegistroCBLC / 100
                                        WHERE CD_BOLSA  = :CodigoBolsa 
                                        AND   CD_MERCAD = :Mercado
                                        AND   CD_MERORI = :MercadoOrigem
                                        AND   TP_CLIE   = :Cliente
                                        AND   TP_LIQUID = :Liquidacao
                                        AND   CD_NATOPE = :NaturezaOperacao
                                        AND   TP_OPER   = :Operacao
                                        AND   TP_PAPEL  = :Papel 
                                        AND   DT_PREINI = :DataInicial ");                                 
                
                #endregion

                #region Dados
                    if (request.PercEmoluentoBovespa != null)
                        this.DataBase.AddInParameter(command, "PercEmoluentoBovespa", DbType.Decimal, request.PercEmoluentoBovespa);

                    if (request.PercEmoluentoCBLC != null)
                        this.DataBase.AddInParameter(command, "PercEmoluentoCBLC", DbType.Decimal, request.PercEmoluentoCBLC);

                    if (request.PercRegistroBovespa != null)
                        this.DataBase.AddInParameter(command, "PercRegistroBovespa", DbType.Decimal, request.PercRegistroBovespa);

                    if (request.PercRegistroCBLC != null)
                        this.DataBase.AddInParameter(command, "PercRegistroCBLC", DbType.Decimal, request.PercRegistroCBLC);
                #endregion

                #region Filtro

                   this.DataBase.AddInParameter(command, "CodigoBolsa", DbType.String, request.CodigoBolsa);
                   this.DataBase.AddInParameter(command, "Mercado", DbType.String, request.Mercado);
                   this.DataBase.AddInParameter(command, "MercadoOrigem", DbType.String, request.MercadoOrigem);

                   string cliente = EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.Cliente);
                   this.DataBase.AddInParameter(command, "Cliente", DbType.String, cliente);

                   string liquidacao = EnumConverter.ToString<BusinessEntity.Enum.TipoLiquidacao>(request.Liquidacao);
                   this.DataBase.AddInParameter(command, "Liquidacao", DbType.String, liquidacao);

                   string naturezaoperacao = EnumConverter.ToString<BusinessEntity.Enum.TipoNaturezaOperacao>(request.NaturezaOperacao);
                   this.DataBase.AddInParameter(command, "NaturezaOperacao", DbType.String, naturezaoperacao);

                   string operacao = EnumConverter.ToString<BusinessEntity.Enum.TipoOperacao>(request.Operacao);
                   this.DataBase.AddInParameter(command, "Operacao", DbType.String, operacao);

                   string papel = EnumConverter.ToString<BusinessEntity.Enum.TipoPapel>(request.Papel);
                   this.DataBase.AddInParameter(command, "Papel", DbType.String, papel);

                   this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial);

                #endregion

               command.CommandText = sql.ToString();
               this.DataBase.ExecuteNonQuery(command);
            }
        }
        #endregion

        #region [PUB] ConsultarTaxas      
        /// <summary>
        /// ConsultarTaxas
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<BusinessEntity.Taxa> ConsultarTaxas(BusinessEntity.Parameter.FiltroTaxa request)
        {
            List<BusinessEntity.Taxa> responses = null;
            StringBuilder sql = new StringBuilder();
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                #region SQL
                sql.Append(@"SELECT 
                                TORTXEMU.CD_NATOPE AS NaturezaOperacao,
                                TORTXEMU.DT_PREINI AS DataInicial,
                                TORTXEMU.DT_PREFIM AS DataFinal,
                                TORTXEMU.CD_BOLSA AS CodigoBolsa,
                                TGEBOLSA.NM_RESU_BOLSA AS NomeBolsa,
                                TORTXEMU.CD_MERCAD AS CodigoMercado,
                                TBOMERCADO.DS_MERCADO AS NomeMercado,
                                TORTXEMU.CD_MERORI AS CodigoMercadoOrigem,
                                TBOMERCADOORIG.DS_MERCADO AS NomeMercadoOrigem,
                                TORTXEMU.TP_OPER AS TipoOperacao,
                                TORTXEMU.TP_PAPEL AS TipoPapel,
                                TORTXEMU.TP_CLIE AS TipoCliente,
                                TORTXEMU.TP_LIQUID AS TipoLiquidacao,
                                NVL(TORTXEMU.NR_EMUBOV,0) * 100 AS PorcTaxaEmoluentosBovespa,
                                NVL(TORTXEMU.NR_EMUCBL,0) * 100 AS PorcTaxaEmoluentosCBLC,
                                NVL(TORTXEMU.NR_REGBOV,0) * 100 AS PorcTaxaRegistroBovespa,
                                NVL(TORTXEMU.NR_REGCBL,0) * 100 AS PorcTaxaRegistroCBLC 
                                FROM TORTXEMU
                                INNER JOIN TGEBOLSA ON TGEBOLSA.CD_BOLSA = TORTXEMU.CD_BOLSA
                                INNER JOIN TBOMERCADO ON TBOMERCADO.CD_ALMERC = TORTXEMU.CD_MERCAD
                                INNER JOIN TBOMERCADO TBOMERCADOORIG ON TBOMERCADOORIG.CD_ALMERC = TORTXEMU.CD_MERORI ");
                #endregion

                #region Filtro

                if (!string.IsNullOrEmpty(request.Bolsa))
                {
                    sql.Append(" AND TORTXEMU.CD_BOLSA = :Bolsa  ");
                    this.DataBase.AddInParameter(command, "Bolsa", DbType.String, request.Bolsa);                      }

                if (!string.IsNullOrEmpty(request.Mercado))
                {
                    sql.Append(" AND TORTXEMU.CD_MERCAD = :Mercado ");

                    this.DataBase.AddInParameter(command, "Mercado", DbType.String, request.Mercado);
                }

                if (!string.IsNullOrEmpty(request.MercadoOrigem))
                {
                    sql.Append(" AND TORTXEMU.CD_MERORI = :MercadoOrigem ");
              
                    this.DataBase.AddInParameter(command, "MercadoOrigem", DbType.String, request.MercadoOrigem);
                }

                if (request.Periodo != null)
                {
                    if(request.Periodo.DataInicial != null)
                    {
                        sql.Append(" AND TORTXEMU.DT_PREINI >= :DataInicial ");
                        
                        this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial);
                    }
                    if(request.Periodo.DataFinal != null)
                    {
                        sql.Append(" AND TORTXEMU.DT_PREFIM <= :DataFinal ");
                        
                        this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.Periodo.DataFinal);
                    }
                }

                 if (request.NaturezaOperacao != null)
                {
                    sql.Append(" AND TORTXEMU.CD_NATOPE = :TipoNatureza ");
                    
                    string naturezaoperacao = EnumConverter.ToString<BusinessEntity.Enum.TipoNaturezaOperacao>(request.NaturezaOperacao.Value);
                    this.DataBase.AddInParameter(command, "TipoNatureza", DbType.String, naturezaoperacao);
                }

                #endregion

                command.CommandText = sql.ToString();

                responses = this.FindAll<BusinessEntity.Taxa>(command, reader =>
                {
                    BusinessEntity.Taxa item = new BusinessEntity.Taxa();

                    item.NaturezaOperacao = EnumConverter.Parse<BusinessEntity.Enum.TipoNaturezaOperacao>(reader["NaturezaOperacao"].ToString());

                    item.Periodo = new Sinacor.Servico.Bovespa.Corretagem.BusinessEntity.Periodo {
                                        DataInicial = reader.GetValue<DateTime?>("DataInicial"),
                                        DataFinal = reader.GetValue<DateTime?>("DataFinal")
                                    };
                    

                    item.CodigoBolsa = reader["CodigoBolsa"].ToString();
                    item.NomeBolsa = reader["NomeBolsa"].ToString();
                    
                    item.Mercado = reader["CodigoMercado"].ToString();
                    item.NomeMercado = reader["NomeMercado"].ToString();

                    item.MercadoOrigem = reader["CodigoMercadoOrigem"].ToString();

                    item.NomeMercadoOrigem = reader["NomeMercadoOrigem"].ToString();

                    item.Operacao = EnumConverter.Parse<BusinessEntity.Enum.TipoOperacao>(reader["TipoOperacao"].ToString());
                  
                    item.Papel = EnumConverter.Parse<BusinessEntity.Enum.TipoPapel>(reader["TipoPapel"].ToString());
                    item.Cliente = EnumConverter.Parse<BusinessEntity.Enum.TipoCliente>(reader["TipoCliente"].ToString());

                    item.Liquidacao = EnumConverter.Parse<BusinessEntity.Enum.TipoLiquidacao>(reader["TipoLiquidacao"].ToString());

                    item.PercEmoluentoBovespa = reader.GetValue<Decimal?>("PorcTaxaEmoluentosBovespa");
                    item.PercEmoluentoCBLC = reader.GetValue<Decimal?>("PorcTaxaEmoluentosCBLC");

                    item.PercRegistroBovespa = reader.GetValue<Decimal?>("PorcTaxaRegistroBovespa");
                    item.PercRegistroCBLC = reader.GetValue<Decimal?>("PorcTaxaRegistroCBLC");

                    return item;
                });
            }

            return responses;
        }

        #endregion

        #region [PUB] ExcluirTaxa
        public void ExcluirTaxa(BusinessEntity.ChaveTaxa request)
        {
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                #region SQL
                StringBuilder sql = new StringBuilder();

                sql.Append(@" DELETE FROM TORTXEMU 
                                    WHERE CD_BOLSA = :CodigoBolsa AND
                                    CD_MERCAD = :Mercado AND
                                    CD_MERORI = :MercadoOrigem AND
                                    TP_CLIE = :Cliente AND
                                    TP_LIQUID = :Liquidacao AND
                                    CD_NATOPE = :NaturezaOperacao AND
                                    TP_OPER = :Operacao AND
                                    TP_PAPEL = :Papel AND
                                    DT_PREINI = :DataInicial ");

                #endregion

                #region Filtro

                this.DataBase.AddInParameter(command, "CodigoBolsa", DbType.String, request.CodigoBolsa);

                this.DataBase.AddInParameter(command, "Mercado", DbType.String, request.Mercado);

                this.DataBase.AddInParameter(command, "MercadoOrigem", DbType.String, request.MercadoOrigem);

                string cliente = EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.Cliente);
                this.DataBase.AddInParameter(command, "Cliente", DbType.String, cliente);

                string liquidacao = EnumConverter.ToString<BusinessEntity.Enum.TipoLiquidacao>(request.Liquidacao);
                this.DataBase.AddInParameter(command, "Liquidacao", DbType.String, liquidacao);

                string naturezaoperacao = EnumConverter.ToString<BusinessEntity.Enum.TipoNaturezaOperacao>(request.NaturezaOperacao);
                this.DataBase.AddInParameter(command, "NaturezaOperacao", DbType.String, naturezaoperacao);

                string operacao = EnumConverter.ToString<BusinessEntity.Enum.TipoOperacao>(request.Operacao);
                this.DataBase.AddInParameter(command, "Operacao", DbType.String, operacao);

                string papel = EnumConverter.ToString<BusinessEntity.Enum.TipoPapel>(request.Papel);
                this.DataBase.AddInParameter(command, "Papel", DbType.String, papel);

                this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial);
               
                #endregion

                command.CommandText = sql.ToString();
                this.DataBase.ExecuteNonQuery(command);
            }
        }
        #endregion

        #region [PUB] IncluirTaxa
        public void IncluirTaxa(BusinessEntity.Taxa request)
        {
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                #region SQL
                StringBuilder sql = new StringBuilder();

                sql.Append(@" INSERT INTO TORTXEMU (CD_NATOPE,DT_PREINI,DT_PREFIM,CD_BOLSA,CD_MERCAD,CD_MERORI,TP_OPER,TP_PAPEL,TP_CLIE,TP_LIQUID,NR_EMUBOV,NR_EMUCBL,NR_REGBOV,NR_REGCBL)
VALUES (:NaturezaOperacao,:DataInicial,:DataFinal,:CodigoBolsa,:Mercado,:MercadoOrigem,:Operacao,:Papel,:Cliente,:Liquidacao,:PercEmoluentoBovespa,:PercEmoluentoCBLC,:PercRegistroBovespa,:PercRegistroCBLC) ");

                #endregion

                #region Dados

                string naturezaoperacao = EnumConverter.ToString<BusinessEntity.Enum.TipoNaturezaOperacao>(request.NaturezaOperacao);
                this.DataBase.AddInParameter(command, "NaturezaOperacao", DbType.String, naturezaoperacao);
                
                if (request.Periodo != null)
                {
                    if (request.Periodo.DataInicial != null)                        
                        this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial);

                    if (request.Periodo.DataFinal != null)
                        this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.Periodo.DataFinal);
                }
                this.DataBase.AddInParameter(command, "CodigoBolsa", DbType.String, request.CodigoBolsa);
                this.DataBase.AddInParameter(command, "Mercado", DbType.String, request.Mercado);
                this.DataBase.AddInParameter(command, "MercadoOrigem", DbType.String, request.MercadoOrigem);

                string operacao = EnumConverter.ToString<BusinessEntity.Enum.TipoOperacao>(request.Operacao);
                this.DataBase.AddInParameter(command, "Operacao", DbType.String, operacao);

                string papel = EnumConverter.ToString<BusinessEntity.Enum.TipoPapel>(request.Papel);
                this.DataBase.AddInParameter(command, "Papel", DbType.String, papel);

                string cliente = EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.Cliente);
                this.DataBase.AddInParameter(command, "Cliente", DbType.String, cliente);

                string liquidacao = EnumConverter.ToString<BusinessEntity.Enum.TipoLiquidacao>(request.Liquidacao);
                this.DataBase.AddInParameter(command, "Liquidacao", DbType.String, liquidacao);

                if (request.PercEmoluentoBovespa != null)
                {
                    decimal percemoluentobovespa = request.PercEmoluentoBovespa.Value / 100;
                    this.DataBase.AddInParameter(command, "PercEmoluentoBovespa", DbType.Decimal, percemoluentobovespa);
                }

                if (request.PercEmoluentoCBLC != null)
                {
                    decimal percemoluentocblc = request.PercEmoluentoCBLC.Value / 100;
                    this.DataBase.AddInParameter(command, "PercEmoluentoCBLC", DbType.Decimal, percemoluentocblc);
                }
                if (request.PercRegistroBovespa != null)
                {
                    decimal percregistrobovespa = request.PercRegistroBovespa.Value / 100;
                    this.DataBase.AddInParameter(command, "PercRegistroBovespa", DbType.Decimal, percregistrobovespa);
                }

                if (request.PercRegistroCBLC != null)
                {
                    decimal percregistrocblc = request.PercRegistroCBLC.Value / 100;
                    this.DataBase.AddInParameter(command, "PercRegistroCBLC", DbType.Decimal, percregistrocblc);
                }
                #endregion

                command.CommandText = sql.ToString();
                this.DataBase.ExecuteNonQuery(command);
            }
        }
        #endregion

        #region [PUB] VerificarConflitoPeriodo

        public Boolean VerificarConflitoPeriodo(BusinessEntity.Taxa request)
        {
            StringBuilder sql = new StringBuilder();
            Int32 total;
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {

                #region SQL
                sql.Append(@"SELECT COUNT(*) 
                            FROM TORTXEMU 
                            WHERE CD_BOLSA = :CodigoBolsa AND
                                  CD_MERCAD = :Mercado AND
                                  CD_MERORI = :MercadoOrigem AND
                                  TP_CLIE = :Cliente AND
                                  TP_LIQUID = :Liquidacao AND
                                  CD_NATOPE = :NaturezaOperacao AND
                                  TP_OPER = :Operacao AND
                                  TP_PAPEL = :Papel AND
                                  ((:DataInicial BETWEEN DT_PREINI AND DT_PREFIM) OR
                                  (:DataFinal BETWEEN DT_PREINI AND DT_PREFIM) OR
                                  (DT_PREINI BETWEEN :DataInicial AND :DataFinal) OR
                                  (DT_PREFIM BETWEEN :DataInicial AND :DataFinal)) ");
                #endregion

                #region Filtro

                    this.DataBase.AddInParameter(command, "CodigoBolsa", DbType.String, request.CodigoBolsa);
                    this.DataBase.AddInParameter(command, "Mercado", DbType.String, request.Mercado);
                    this.DataBase.AddInParameter(command, "MercadoOrigem", DbType.String, request.MercadoOrigem);

                    string cliente = EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.Cliente);
                    this.DataBase.AddInParameter(command, "Cliente", DbType.String, cliente);

                    string liquidacao = EnumConverter.ToString<BusinessEntity.Enum.TipoLiquidacao>(request.Liquidacao);
                    this.DataBase.AddInParameter(command, "Liquidacao", DbType.String, liquidacao);

                    string naturezaoperacao = EnumConverter.ToString<BusinessEntity.Enum.TipoNaturezaOperacao>(request.NaturezaOperacao);
                    this.DataBase.AddInParameter(command, "NaturezaOperacao", DbType.String, naturezaoperacao);

                    string operacao = EnumConverter.ToString<BusinessEntity.Enum.TipoOperacao>(request.Operacao);
                    this.DataBase.AddInParameter(command, "Operacao", DbType.String, operacao);

                    string papel = EnumConverter.ToString<BusinessEntity.Enum.TipoPapel>(request.Papel);
                    this.DataBase.AddInParameter(command, "Papel", DbType.String, papel);

                    this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial); 
                    this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.Periodo.DataFinal);

                    #endregion

                command.CommandText = sql.ToString();
                total = Convert.ToInt32(this.DataBase.ExecuteScalar(command));
            }

            return total > 0? true:false;
        }

        #endregion

        #region [PUB] VerificarExistenciaTaxa

        public bool VerificarExistenciaTaxa(BusinessEntity.Taxa request)
        {
            StringBuilder sql = new StringBuilder();
            Int32 total;
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {

                #region SQL
                sql.Append(@"SELECT 
                            COUNT(*)
                            FROM TORTXEMU
                            INNER JOIN TGEBOLSA ON TGEBOLSA.CD_BOLSA = TORTXEMU.CD_BOLSA
                            INNER JOIN TBOMERCADO ON TBOMERCADO.CD_ALMERC = TORTXEMU.CD_MERCAD
                            INNER JOIN TBOMERCADO TBOMERCADOORIG ON TBOMERCADOORIG.CD_ALMERC = TORTXEMU.CD_MERORI
                            WHERE
                            TORTXEMU.CD_BOLSA = :Bolsa AND
                            TORTXEMU.CD_MERCAD = :Mercado AND
                            TORTXEMU.CD_MERORI = :MercadoOrigem AND
                            TORTXEMU.CD_NATOPE = :NaturezaOperacao AND
                            TORTXEMU.TP_OPER = :TipoOperacao AND
                            TORTXEMU.TP_PAPEL = :TipoPapel AND
                            TORTXEMU.TP_CLIE = :TipoCliente AND
                            TORTXEMU.TP_LIQUID = :TipoLiquidacao AND
                            TORTXEMU.DT_PREINI = :DataInicio ");
                #endregion

                #region Filtro

                this.DataBase.AddInParameter(command, "Bolsa", DbType.String, request.CodigoBolsa);
                this.DataBase.AddInParameter(command, "Mercado", DbType.String, request.Mercado);
                this.DataBase.AddInParameter(command, "MercadoOrigem", DbType.String, request.MercadoOrigem);

                string naturezaoperacao = EnumConverter.ToString<BusinessEntity.Enum.TipoNaturezaOperacao>(request.NaturezaOperacao);
                this.DataBase.AddInParameter(command, "NaturezaOperacao", DbType.String, naturezaoperacao);

                string operacao = EnumConverter.ToString<BusinessEntity.Enum.TipoOperacao>(request.Operacao);
                this.DataBase.AddInParameter(command, "TipoOperacao", DbType.String, operacao);

                string papel = EnumConverter.ToString<BusinessEntity.Enum.TipoPapel>(request.Papel);
                this.DataBase.AddInParameter(command, "TipoPapel", DbType.String, papel);


                string cliente = EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.Cliente);
                this.DataBase.AddInParameter(command, "TipoCliente", DbType.String, cliente);

                string liquidacao = EnumConverter.ToString<BusinessEntity.Enum.TipoLiquidacao>(request.Liquidacao);
                this.DataBase.AddInParameter(command, "TipoLiquidacao", DbType.String, liquidacao);


                this.DataBase.AddInParameter(command, "DataInicio", DbType.DateTime, request.Periodo.DataInicial);
                    
                    #endregion

                command.CommandText = sql.ToString();
                total = Convert.ToInt32(this.DataBase.ExecuteScalar(command));
            }

            return total > 0? true:false;
        }

        #endregion

        #region [PUB] ObterTaxas
        public BusinessEntity.Taxa ObterTaxas(BusinessEntity.ChaveTaxa request)
        {
            BusinessEntity.Taxa response = null;
            StringBuilder sql = new StringBuilder();
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                #region SQL
                sql.Append(@"SELECT 
                                TORTXEMU.CD_NATOPE AS NaturezaOperacao, 
                                TORTXEMU.DT_PREINI AS DataInicial, 
                                TORTXEMU.DT_PREFIM AS DataFinal, 
                                TORTXEMU.CD_BOLSA AS CodigoBolsa, 
                                TGEBOLSA.NM_RESU_BOLSA AS NomeBolsa, 
                                TORTXEMU.CD_MERCAD AS CodigoMercado, 
                                TBOMERCADO.DS_MERCADO AS NomeMercado, 
                                TORTXEMU.CD_MERORI AS CodigoMercadoOrigem,
                                TBOMERCADOORIG.DS_MERCADO AS NomeMercadoOrigem,
                                TORTXEMU.TP_OPER AS TipoOperacao, 
                                TORTXEMU.TP_PAPEL AS TipoPapel, 
                                TORTXEMU.TP_CLIE AS TipoCliente, 
                                TORTXEMU.TP_LIQUID AS TipoLiquidacao, 
                                NVL(TORTXEMU.NR_EMUBOV,0) * 100 AS PorcTaxaEmoluentosBovespa, 
                                NVL(TORTXEMU.NR_EMUCBL,0) * 100 AS PorcTaxaEmoluentosCBLC, 
                                NVL(TORTXEMU.NR_REGBOV,0) * 100 AS PorcTaxaRegistroBovespa,
                                NVL(TORTXEMU.NR_REGCBL,0) * 100 AS PorcTaxaRegistroCBLC 
                                FROM TORTXEMU
                                INNER JOIN TGEBOLSA ON TGEBOLSA.CD_BOLSA = TORTXEMU.CD_BOLSA
                                INNER JOIN TBOMERCADO ON TBOMERCADO.CD_ALMERC = TORTXEMU.CD_MERCAD
                                INNER JOIN TBOMERCADO TBOMERCADOORIG ON TBOMERCADOORIG.CD_ALMERC = TORTXEMU.CD_MERORI
                                WHERE TORTXEMU.CD_BOLSA = :CodigoBolsa AND
                                TORTXEMU.CD_MERCAD = :Mercado AND
                                TORTXEMU.CD_MERORI = :MercadoOrigem AND
                                TORTXEMU.TP_CLIE = :Cliente AND
                                TORTXEMU.TP_LIQUID = :Liquidacao AND
                                TORTXEMU.CD_NATOPE = :NaturezaOperacao AND
                                TORTXEMU.TP_OPER = :Operacao AND
                                TORTXEMU.TP_PAPEL = :Papel AND
                                TORTXEMU.DT_PREINI = :DataInicial
                                ");
                #endregion

                #region Parametros
                this.DataBase.AddInParameter(command, "CodigoBolsa", DbType.String, request.CodigoBolsa);
                this.DataBase.AddInParameter(command, "Mercado", DbType.String, request.Mercado);
                this.DataBase.AddInParameter(command, "MercadoOrigem", DbType.String, request.MercadoOrigem);
                
                string cliente = EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.Cliente);
                this.DataBase.AddInParameter(command, "Cliente", DbType.String, cliente);

                string liquidacao = EnumConverter.ToString<BusinessEntity.Enum.TipoLiquidacao>(request.Liquidacao);
                this.DataBase.AddInParameter(command, "Liquidacao", DbType.String, liquidacao);

                string naturezaoperacao = EnumConverter.ToString<BusinessEntity.Enum.TipoNaturezaOperacao>(request.NaturezaOperacao);
                this.DataBase.AddInParameter(command, "NaturezaOperacao", DbType.String, naturezaoperacao);
                
                string operacao = EnumConverter.ToString<BusinessEntity.Enum.TipoOperacao>(request.Operacao);
                this.DataBase.AddInParameter(command, "Operacao", DbType.String, operacao);

                string papel = EnumConverter.ToString<BusinessEntity.Enum.TipoPapel>(request.Papel);
                this.DataBase.AddInParameter(command, "Papel", DbType.String, papel);

                this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial);
                #endregion
                    
                command.CommandText = sql.ToString();

                response = this.FindOne<BusinessEntity.Taxa>(command, reader =>
                {
                    BusinessEntity.Taxa item = new BusinessEntity.Taxa();

                    item.NaturezaOperacao = EnumConverter.Parse<BusinessEntity.Enum.TipoNaturezaOperacao>(reader["NaturezaOperacao"].ToString());

                    item.Periodo = new Sinacor.Servico.Bovespa.Corretagem.BusinessEntity.Periodo
                    {
                        DataInicial = reader.GetValue<DateTime?>("DataInicial"),
                        DataFinal = reader.GetValue<DateTime?>("DataFinal")
                    };


                    item.CodigoBolsa = reader["CodigoBolsa"].ToString();
                    item.NomeBolsa = reader["NomeBolsa"].ToString();

                    item.Mercado = reader["CodigoMercado"].ToString();
                    item.NomeMercado = reader["NomeMercado"].ToString();

                    item.MercadoOrigem = reader["CodigoMercadoOrigem"].ToString();

                    item.NomeMercadoOrigem = reader["NomeMercadoOrigem"].ToString();

                    item.Operacao = EnumConverter.Parse<BusinessEntity.Enum.TipoOperacao>(reader["TipoOperacao"].ToString());

                    item.Papel = EnumConverter.Parse<BusinessEntity.Enum.TipoPapel>(reader["TipoPapel"].ToString());
                    item.Cliente = EnumConverter.Parse<BusinessEntity.Enum.TipoCliente>(reader["TipoCliente"].ToString());

                    item.Liquidacao = EnumConverter.Parse<BusinessEntity.Enum.TipoLiquidacao>(reader["TipoLiquidacao"].ToString());

                    item.PercEmoluentoBovespa = reader.GetValue<Decimal?>("PorcTaxaEmoluentosBovespa");
                    item.PercEmoluentoCBLC = reader.GetValue<Decimal?>("PorcTaxaEmoluentosCBLC");

                    item.PercRegistroBovespa = reader.GetValue<Decimal?>("PorcTaxaRegistroBovespa");
                    item.PercRegistroCBLC = reader.GetValue<Decimal?>("PorcTaxaRegistroCBLC");

                    return item;
                });
            }

            return response;
        }
        #endregion
    }
}
